using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;
using Garlic.CC.Utility;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

using Garlic.DM.DataModel;

namespace Garlic.DAL
{
	/// <summary>
	/// DataAccess
	/// </summary>
	public partial class MenuDAL
	{
		#region Sql const
		public  const string TM_BAS_MENU_SELECT_BY_ID=" SELECT menu_name,parent_menu_id,order_id,image_url,link_url,comments,create_user,create_time,modify_user,modify_time,menu_id"+
											" FROM TM_BAS_Menu"+
											" WHERE 1=1 " +
											" and  menu_id =  :menu_id";

											
		public  const string TM_BAS_MENU_SELECT=" SELECT "+
											"menu_name,"+
											"parent_menu_id,"+
											"order_id,"+
											"image_url,"+
											"link_url,"+
											"comments,"+
											"create_user,"+
											"create_time,"+
											"modify_user,"+
											"modify_time,"+
											"menu_id"+
                                            " FROM TM_BAS_Menu {0} ";
														
								
	
		public  const string TM_BAS_MENU_INSERT=" INSERT INTO TM_BAS_Menu ("+
											"menu_name,"+
											"parent_menu_id,"+
											"order_id,"+
											"image_url,"+
											"link_url,"+
											"comments,"+
											"create_user,"+
											"create_time,"+
											"modify_user,"+
											"modify_time,"+
											"menu_id"+
										") VALUES ("+
											":menu_name,"+
											":parent_menu_id,"+
											":order_id,"+
											":image_url,"+
											":link_url,"+
											":comments,"+
											":create_user,"+
											":create_time,"+
											":modify_user,"+
											":modify_time,"+
											":menu_id"+
										")";
									
									
		public  const string TM_BAS_MENU_UPDATE=" UPDATE TM_BAS_Menu SET "+
											" menu_name =  :menu_name, "+
											" parent_menu_id =  :parent_menu_id, "+
											" order_id =  :order_id, "+
											" image_url =  :image_url, "+
											" link_url =  :link_url, "+
											" comments =  :comments, "+
											" create_user =  :create_user, "+
											" create_time =  :create_time, "+
											" modify_user =  :modify_user, "+
											" modify_time =  :modify_time "+
										" WHERE 1=1 " +
											" and  menu_id =  :menu_id";
										
	
	
		public  const string TM_BAS_MENU_DELETE=" DELETE FROM TM_BAS_Menu" +
										" WHERE 1=1"+
											" and  menu_id =  :menu_id";

        public const string sql_menu_user = @"SELECT 
[TM_BAS_Menu].[menu_name],
[TM_BAS_Menu].[parent_menu_id],
[TM_BAS_Menu].[order_id],
[TM_BAS_Menu].[image_url],
[TM_BAS_Menu].[link_url],
[TM_BAS_Menu].[comments],
[TM_BAS_Menu].[create_user],
[TM_BAS_Menu].[create_time],
[TM_BAS_Menu].[modify_user],
[TM_BAS_Menu].[modify_time],
[TM_BAS_Menu].[menu_id],
[TS_BAS_Auth_User].[is_effective]
 FROM [psprd].[TM_BAS_Menu]  inner join [psprd].[TS_BAS_Auth_User]
 on [TM_BAS_Menu].[menu_id] = [TS_BAS_Auth_User].[resource_id]
 where [TS_BAS_Auth_User].[resource_type]='Menu' {0}";								
		
		#endregion
		
		/// <summary>
		/// Get MenuInfo by menu_id
		/// </summary>
		/// <param name="menu_id">MenuInfo </param>
		/// <returns></returns>
		public static MenuInfo GetInfo(int menu_id)
		{
	
				Database db = DatabaseFactory.CreateDatabase();
				DbCommand dbCommand = db.GetSqlStringCommand(TM_BAS_MENU_SELECT_BY_ID);
			db.AddInParameter(dbCommand, "menu_id", DbType.Int32,menu_id);
				

				using (IDataReader dr = db.ExecuteReader(dbCommand))
				{
					if (dr.Read())
					{
						return CreateMenuInfo(dr);
					}
					else
						return null;
				}
		}
	
	
		/// <summary>
		/// Get data list
		/// </summary>
		/// <param name="textWhere">conditon</param>
		/// <param name="orderText">sort</param>
		/// <returns>MenuInfo list </returns>
		public static List<MenuInfo> GetList(string textWhere,string orderText)
		{
			string query=string.Empty;
			if(!string.IsNullOrEmpty(textWhere))
			{
			query+=" where "+textWhere; 
			}

            if (!string.IsNullOrEmpty(orderText))
            {
                query += " order by " + orderText;
            }
			
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(string.Format(TM_BAS_MENU_SELECT,query));
			
			return GetList(string.Format(TM_BAS_MENU_SELECT,query));
			
		}

        public static List<MenuInfo> GetList_User(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
            {
                query += "  and [TS_BAS_Auth_User].user_id=  " + textWhere;
            }

            if (!string.IsNullOrEmpty(orderText))
            {
                query += " order by " + orderText;
            }

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(sql_menu_user, query));

            return GetList(string.Format(sql_menu_user, query));

        }
		
		/// <summary>
		/// Get data list
		/// </summary>
		/// <param name="sql">SQL statement</param>
		/// <returns>MenuInfo list </returns>
		public static List<MenuInfo> GetList(string sql)
		{
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(sql);
			List<MenuInfo> list=new List<MenuInfo>();
			using (IDataReader dr = db.ExecuteReader(dbCommand))
			{
				while(dr.Read())
				{
					list.Add(CreateMenuInfo(dr));
				}
			}
			
			return list;
		}

        public static List<MenuInfo> GetMenusByUserID(string textWhere)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
            {
                query += " and is_effective=1  and [TS_BAS_Auth_User].user_id=  " + textWhere;
            }

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(sql_menu_user, query));

            return GetList(string.Format(sql_menu_user, query));
        }
	
		/// <summary>
		/// Add
		/// </summary>
		/// <param name="menuInfo"> entity</param>
		public static int Add(MenuInfo menuInfo)
		{
				Database db = DatabaseFactory.CreateDatabase();
	
				DbCommand dbCommand = db.GetSqlStringCommand(TM_BAS_MENU_INSERT);
				
				db.AddInParameter(dbCommand, "menu_name",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Menu_name)); 
				db.AddInParameter(dbCommand, "parent_menu_id",DbType.Int32,DBConvert.ToDBValue(menuInfo.Parent_menu_id)); 
				db.AddInParameter(dbCommand, "order_id",DbType.Int32,DBConvert.ToDBValue(menuInfo.Order_id)); 
				db.AddInParameter(dbCommand, "image_url",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Image_url)); 
				db.AddInParameter(dbCommand, "link_url",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Link_url)); 
				db.AddInParameter(dbCommand, "comments",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Comments)); 
				db.AddInParameter(dbCommand, "create_user",DbType.Int32,DBConvert.ToDBValue(menuInfo.Create_user)); 
				db.AddInParameter(dbCommand, "create_time",DbType.DateTime,DBConvert.ToDBValue(menuInfo.Create_time)); 
				db.AddInParameter(dbCommand, "modify_user",DbType.Int32,DBConvert.ToDBValue(menuInfo.Modify_user)); 
				db.AddInParameter(dbCommand, "modify_time",DbType.DateTime,DBConvert.ToDBValue(menuInfo.Modify_time)); 
				db.AddInParameter(dbCommand, "menu_id",DbType.Int32,menuInfo.Menu_id); 
				
				return int.Parse("0"+db.ExecuteScalar(dbCommand));
		}
		
		/// <summary>
		/// Update
		/// </summary>
		/// <param name="menuInfo"> utility</param>
		public static void Update(MenuInfo menuInfo)
		{
			Database db = DatabaseFactory.CreateDatabase();
	
				DbCommand dbCommand = db.GetSqlStringCommand(TM_BAS_MENU_UPDATE);
				
				db.AddInParameter(dbCommand, "menu_name",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Menu_name)); 
				db.AddInParameter(dbCommand, "parent_menu_id",DbType.Int32,DBConvert.ToDBValue(menuInfo.Parent_menu_id)); 
				db.AddInParameter(dbCommand, "order_id",DbType.Int32,DBConvert.ToDBValue(menuInfo.Order_id)); 
				db.AddInParameter(dbCommand, "image_url",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Image_url)); 
				db.AddInParameter(dbCommand, "link_url",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Link_url)); 
				db.AddInParameter(dbCommand, "comments",DbType.AnsiString,DBConvert.ToDBValue(menuInfo.Comments)); 
				db.AddInParameter(dbCommand, "create_user",DbType.Int32,DBConvert.ToDBValue(menuInfo.Create_user)); 
				db.AddInParameter(dbCommand, "create_time",DbType.DateTime,DBConvert.ToDBValue(menuInfo.Create_time)); 
				db.AddInParameter(dbCommand, "modify_user",DbType.Int32,DBConvert.ToDBValue(menuInfo.Modify_user)); 
				db.AddInParameter(dbCommand, "modify_time",DbType.DateTime,DBConvert.ToDBValue(menuInfo.Modify_time)); 
				db.AddInParameter(dbCommand, "menu_id",DbType.Int32,menuInfo.Menu_id); 
				
				db.ExecuteNonQuery(dbCommand);
		}
		
		/// <summary>
		/// Delete
		/// </summary>
		/// <param name="menu_id">TM_BAS_Menu Number</param>
		/// <returns></returns>
		public static void Delete(int menu_id)
		{
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(TM_BAS_MENU_DELETE);
			db.AddInParameter(dbCommand, "menu_id", DbType.Int32,menu_id);
			db.ExecuteNonQuery(dbCommand);
		}
		
	

	
		private static MenuInfo CreateMenuInfo(IDataReader rdr)
		{
			MenuInfo mymenuInfo = new MenuInfo();
			mymenuInfo.Menu_name=DBConvert.GetString(rdr,0);
			mymenuInfo.Parent_menu_id=DBConvert.GetInt32(rdr,1);
			mymenuInfo.Order_id=DBConvert.GetInt32(rdr,2);
			mymenuInfo.Image_url=DBConvert.GetString(rdr,3);
			mymenuInfo.Link_url=DBConvert.GetString(rdr,4);
			mymenuInfo.Comments=DBConvert.GetString(rdr,5);
			mymenuInfo.Create_user=DBConvert.GetInt32(rdr,6);
			mymenuInfo.Create_time=DBConvert.GetDateTime(rdr,7);
			mymenuInfo.Modify_user=DBConvert.GetInt32(rdr,8);
			mymenuInfo.Modify_time=DBConvert.GetDateTime(rdr,9);
			mymenuInfo.Menu_id=rdr.GetInt32(10);
      

			return mymenuInfo;
		}
	}
}
